package com.fdms.aurora;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel {
	
	private Properties properties = new Properties();
	
	private static final String FIRST_CALL_INFORMATION = "First Call Information";
	private static final String DECEASED_INFORMATION = "Deceased Information";
	private static final String HOMECHAPEL_INFORMATION = "HomeChapel Information";
	private static final String FACILITY_INFORMATION = "Facility Information";
	private static final String INFORMANT_INFORMATION = "Informant Information";
	private static final String NEXT_OF_KIN = "Next of Kin";		
	private static final String EXECUTOR = "Executor";

	public ReadExcel() {
		try {
			properties.load(new FileInputStream(new File("atneed.properties")));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public String getAtNeedCase() {
		
		StringBuffer query = new StringBuffer("INSERT INTO vitalstats(");
		
		try {
			InputStream myxls = new FileInputStream("Book2.xlsx");
			XSSFWorkbook wb     = new XSSFWorkbook(myxls);
			
			XSSFSheet sheet = wb.getSheet(FIRST_CALL_INFORMATION);       // first sheet
			XSSFRow row     = sheet.getRow(0);        // third row
			XSSFRow data    = sheet.getRow(1);        // third row
			
			for(int index = 0; index < 9; index++) {
				String attr = properties.getProperty((FIRST_CALL_INFORMATION + "." + row.getCell(index + 1).getRawValue()).replaceAll(" ", ""));
				//header[index] = row.getCell(index + 1).getRawValue();
				if(index < 8)
					query.append(attr + ",");
				else
					query.append(attr + ") VALUES (");
			}
			
			for(int index = 0; index < 9; index++) {
				String dataValue = "'" + data.getCell(index + 1).getRawValue() + "'";
				//header[index] = row.getCell(index + 1).getRawValue();
				if(index < 8)
					query.append(dataValue + ",");
				else
					query.append(dataValue + ");");
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return query.toString();
	}
}
